package DAO;

import entity.db.CitationTextDB;
import entity.db.PaperDB;
import entity.db.ReferencestableDB;
import entity.text.PaperEntity;
import util.JDBCUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

/**
 * @author ：hzs
 * @date ：Created in 2020/12/4 15:09
 * @description：
 * @modified By：
 * @version: $
 */
public class CitationDAO {

    public List<List<Object>> getArgumentChainCitationAdjacency() {

        String sql = "SELECT paperid, referedid, label " +
                "FROM referencestable " +
                "INNER JOIN CHAIN AS a ON referencestable.`paperid` = a.`id`" +
                "INNER JOIN CHAIN AS b ON referencestable.`referedid` = b.`id`";
        try(Connection connection = JDBCUtil.getConnection()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet resultSet1 = statement.executeQuery(sql);
                List<List<Object>> result = new ArrayList<>();
                while (resultSet1.next()) {
                    List<Object> arc = new ArrayList<>();
                    arc.add(resultSet1.getInt(1));
                    arc.add(resultSet1.getInt(2));
                    arc.add(resultSet1.getString(3));
                    result.add(arc);
                }
                return result;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;

    }

    public List<ReferencestableDB> getCitationAdjacencyBycommunityid(int communityid) {
        String sql = "SELECT paperid, referedid, Label FROM " +
                "(SELECT paperid, referedid, Label FROM referencestable t1, paper t2 WHERE t2.`communityid` = " +
                communityid + " AND  t1.`paperid` = t2.`id` AND t1.`unavailable_paper` = 0) t3, paper t4 " +
                "WHERE t3.`referedid` = t4.`id`";
        try(Connection connection = JDBCUtil.getConnection()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet resultSet1 = statement.executeQuery(sql);
                List<ReferencestableDB> result = new ArrayList<>();
                while (resultSet1.next()) {
                    ReferencestableDB referencestableDB = new ReferencestableDB();
                    referencestableDB.setPaperid(resultSet1.getString(1));
                    referencestableDB.setReferedid(resultSet1.getString(2));
                    referencestableDB.setLabel(resultSet1.getString(3));
                    result.add(referencestableDB);
                }
                return result;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * k：文章ID，V：文件码
     * @param ids
     * @return
     */
    public Map<String, String> getFileNameByIDs(List<String> ids) {
        if (ids == null && ids.size() == 0) {
            return null;
        }
        StringBuilder sql = new StringBuilder("SELECT id, xmlfile FROM paper WHERE id IN (");
        Iterator<String> iterator = ids.iterator();
        boolean hasNext = iterator.hasNext();
        while (hasNext) {
            sql.append(iterator.next());
            hasNext = iterator.hasNext();
            if (hasNext) {
                sql.append(",");
            }
        }
        sql.append(")");
        try(Connection connection = JDBCUtil.getConnection()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet resultSet1 = statement.executeQuery(sql.toString());
                Map<String, String> result = new HashMap<>(ids.size());
                while (resultSet1.next()) {
                    String id = resultSet1.getString(1);
                    String code = resultSet1.getString(2);
                    if (code.contains(".")) {
                        code = code.split("\\.")[0];
                    }
                    result.put(id, code);
                }
                return result;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public PaperEntity getPaperInformationFromFile(String fileName) {
        String sql = "SELECT id, title, abstract, keywords FROM paper WHERE xmlfile like \"%" +
                fileName + "%\" limit 1";
        try(Connection connection = JDBCUtil.getConnection()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet resultSet1 = statement.executeQuery(sql);
                if (resultSet1.next()) {
                    PaperEntity paperEntity = new PaperEntity();
                    paperEntity.setId(resultSet1.getInt(1));
                    paperEntity.setTitle(resultSet1.getString(2));
                    paperEntity.setPaperAbstract(resultSet1.getString(3));
                    paperEntity.setKeywords(resultSet1.getString(4));
                    return paperEntity;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public Map<String, List<String>> getCitationText() {
        String sql = "SELECT source, target FROM citation_text GROUP BY source, target";
        try(Connection connection = JDBCUtil.getConnection()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet resultSet1 = statement.executeQuery(sql);
                Map<String, List<String>> result = new HashMap<>();
                while (resultSet1.next()) {
                    List<String> arc = new ArrayList<>();
                    String source = resultSet1.getString(1);
                    String target = resultSet1.getString(2);
                    arc.add(source);
                    arc.add(target);
                    String key = source + "," + target;
                    result.put(key, arc);
                }
                return result;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public boolean insertCitationText(List<CitationTextDB> citationTextList) {
        Connection connection = JDBCUtil.getConnection();
        StringBuilder sql = new StringBuilder("INSERT INTO `citation_text` (source, target, citation_index, citation, similarity, distribution, noun_phrase) VALUE");
//                source + "," + target + ",'" + citationIndex + "','"+ similarity + "','" + value + "','" + s + "')";
        String format = "(%s, %s, '%s', '%s', '%s', '%s', '%s')";
        Iterator<CitationTextDB> iterator = citationTextList.iterator();
        while (iterator.hasNext()) {
            CitationTextDB next = iterator.next();
            sql.append(String.format(format
                    ,next.getSource()
                    ,next.getTarget()
                    ,next.getCitationIndex()
                    ,next.getCitation()
                    ,next.getSimilarity()
//                    ,next.getDistribution()
//                    ,next.getNounPhrase()
            ));
            if (iterator.hasNext()) {
                sql.append(",");
            }
        }
        try {
            Statement statement = connection.createStatement();
            boolean execute = statement.execute(sql.toString());
            return execute;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    /**
     * 根据输出的id将deleted字段改为1
     * @param ids
     * @return
     */
    public int updateFieldDeletedById(List<String> ids) {
        StringBuilder idInSql = new StringBuilder("(");
        Iterator<String> iterator = ids.iterator();
        while (iterator.hasNext()) {
            String next = iterator.next();
            idInSql.append(next);
            if (iterator.hasNext()) {
                idInSql.append(",");
            }
        }
        idInSql.append(") limit ").append(ids.size());
        String sql = "UPDATE citation_text SET deleted = 1 WHERE id IN ".concat(idInSql.toString());
        return executeWriteSql(sql);
    }

    public List<CitationTextDB> getAllCitationText() {
        String sql = "SELECT id, source, target, citation_index, citation, similarity, distribution, noun_phrase, deleted FROM citation_text";
        List<List<Object>> lists = executeReadSql(sql);
        List<CitationTextDB> result = new ArrayList<>();
        for (List<Object> list : lists) {
            CitationTextDB citationTextDB = new CitationTextDB();
            citationTextDB.setId((int)list.get(0));
            citationTextDB.setSource((int)list.get(1));
            citationTextDB.setTarget((int)list.get(2));
            citationTextDB.setCitationIndex((String)list.get(3));
            citationTextDB.setCitation((String)list.get(4));
            citationTextDB.setSimilarity((String)list.get(5));
//            citationTextDB.setDistribution((String)list.get(6));
//            citationTextDB.setNounPhrase((String)list.get(7));
            citationTextDB.setDeleted((int)list.get(8));
            result.add(citationTextDB);
        }
        return result;
    }

    /**
     * 执行写操作的SQL语句
     */
    public int executeWriteSql(String sql) {
        try(Connection connection = JDBCUtil.getConnection()) {
            try (Statement statement = connection.createStatement()) {
                int execute = statement.executeUpdate(sql);
                return execute;
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 执行读操作的SQL语句
     */
    public List<List<Object>> executeReadSql(String sql) {
        try(Connection connection = JDBCUtil.getConnection()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet resultSet1 = statement.executeQuery(sql);
                List<List<Object>> result = new ArrayList<>();
                while (resultSet1.next()) {
                    int columnCount = resultSet1.getMetaData().getColumnCount();
                    List<Object> line = new ArrayList<>();
                    for (int i = 1; i <= columnCount; i++) {
                        line.add(resultSet1.getObject(i));
                    }
                    result.add(line);
                }
                return result;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void main(String[] args) {
        List<String> ids = new ArrayList<>();
        ids.add("1");
        ids.add("2");
        List<CitationTextDB> allCitationText = new CitationDAO().getAllCitationText();
        System.out.println("====");
    }
}
